package xyz.xfcloud.utils.excel;

import com.google.common.collect.Maps;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.util.*;
/**
 * 现在思路是根据字段的属性名设置值
 * 判断字段类型，获取指定格式
 */


/**
 * 导入Excel映射为Java实体类
 */
public class ImportExcelUtils {
    static {
        DateConverter converter = new DateConverter();
        converter.setPattern(new String("yyyy-MM-dd"));
        ConvertUtils.register(converter, Date.class);
    }

    /**
     * 导入Excel，返回Java实体
     * <p>
     * Java实体的属性必须加@ExcelNote注解，并且desc和Excel标题一致
     *
     * @param inputStream 文件流
     * @param claz        Java实体Class
     * @return
     */
    public static <T> List<T> excel2Pojo(InputStream inputStream, Class<T> claz) {
        try (Workbook wb = WorkbookFactory.create(inputStream)) {
            Sheet sheet = wb.getSheetAt(0);
            //生成属性和列对应关系的map，Map<类属性名，对应一行的第几列>
            Map<String, Integer> propertyMap = generateColumnPropertyMap(sheet, getEntityAlias(claz));
            //根据指定的映射关系进行转换
            List<T> pojoList = generateList(sheet, propertyMap, claz);
            return pojoList;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据Java类class获取属性映射关系Map
     *
     * @param clazz Java类class
     * @return
     */
    private static Map<String, String> getEntityAlias(Class clazz) {
        Map<String, String> map = Maps.newLinkedHashMap();
        Field[] fields = clazz.getDeclaredFields();
        try {
            for (Field field : fields) {
                field.setAccessible(true);
                map.put((field.getAnnotation(ExcelNote.class)).desc(), field.getName());
            }
        } catch (Exception ex) {
            System.out.println("字段解析出错：" + ex.toString());
        }
        return map;
    }

    /**
     * 生成一个属性-列的对应关系的map
     *
     * @param sheet 表
     * @param alias 别名
     * @return
     */
    private static Map<String, Integer> generateColumnPropertyMap(Sheet sheet, Map<String, String> alias) {
        Map<String, Integer> propertyMap = Maps.newHashMap();
        Row propertyRow = sheet.getRow(0);
        short firstCellNum = propertyRow.getFirstCellNum();
        short lastCellNum = propertyRow.getLastCellNum();

        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell cell = propertyRow.getCell(i);
            cell.setCellType(CellType.STRING);
            if (cell == null) {
                continue;
            }
            //列名
            String cellValue = cell.getStringCellValue();
            //对应属性名
            String propertyName = alias.get(cellValue);
            propertyMap.put(propertyName, i);
        }
        return propertyMap;
    }

    /**
     * 根据指定关系将表数据转换成对象数组
     *
     * @param sheet       表
     * @param propertyMap 属性映射关系Map<"属性名",一行第几列>
     * @param claz        类类型
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws InvocationTargetException
     */
    private static <T> List<T> generateList(Sheet sheet, Map<String, Integer> propertyMap, Class<T> claz) throws InstantiationException,
            IllegalAccessException, InvocationTargetException {
        //对象数组
        List<T> pojoList = new ArrayList<>();
        for (Row row : sheet) {
            //跳过表头
            if (row.getRowNum() < 1) {
                continue;
            }
            T instance = claz.newInstance();
            Set<Map.Entry<String, Integer>> entrySet = propertyMap.entrySet();
            for (Map.Entry<String, Integer> entry : entrySet) {
                //获取此行指定列的值,即为属性对应的值
                Cell cell = row.getCell(entry.getValue());
                CellType type = cell.getCellTypeEnum();
                String property = StringUtils.EMPTY;
                switch (type) {
                    case STRING:
                        property = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        property = checkDate(cell);
                        break;
                    default:
                        break;
                }
                BeanUtils.setProperty(instance, entry.getKey(), property);
            }
            pojoList.add(instance);
        }
        return pojoList;
    }

    /**
     * 日期类型也是数字，检查是日期还是数字
     *
     * @param cell
     * @return
     */
    private static String checkDate(Cell cell) {
        String format = cell.getCellStyle().getDataFormatString();
        if ("mmm-yy".equals(format) || "yyyy/mm;@".equals(format) || "m/d/yy".equals(format) || "yy/m/d".equals(format) ||
                "mm/dd/yy".equals(format) || "dd-mmm-yy".equals(format) || "yyyy/m/d".equals(format)) {
            return xyz.xfcloud.utils.DateUtil.date(cell.getDateCellValue());
        }
        if ("h:mm:ss".endsWith(format) || "h:mm".equals(format) || "h:mm:ss AM/PM".equals(format)
                || "h:mm AM/PM".equals(format) || "m/d/yy h:mm".equals(format) || "[h]:mm:ss".equals(format)
                || "mm:ss".equals(format) || "hh:mm".equals(format) || "yyyy/m/d hh:mm".equals(format) || "hh:mm:ss".equals(format)
                || "yyyy-m-d hh:mm".equals(format)) {
            return xyz.xfcloud.utils.DateUtil.dateTime(cell.getDateCellValue());
        }
        return String.valueOf(((Double) cell.getNumericCellValue()).intValue());
    }

//    public static void main(String[] args) throws Exception {
//        FileInputStream fis = new FileInputStream("/Users/pengchao/01works/test.xlsx");
//        List<User> out = ImportExcelUtil.excel2Pojo(fis, User.class);
//
//        System.out.println(out);
//
////        @Data
////        public class User {
////            @ExcelNote(desc = "姓名")
////            private String name;
////            @ExcelNote(desc = "年龄")
////            private Integer age;
////        }
//    }
}
